package weaver.interfaces.dsjj.duss.mailCronJob;

import weaver.conn.RecordSet;
import weaver.conn.RecordSetDataSource;
import weaver.general.BaseBean;
import weaver.general.StaticObj;
import weaver.general.Util;
import weaver.interfaces.datasource.DataSource;
import weaver.interfaces.dsjj.duss.TransMethod;
import weaver.interfaces.schedule.BaseCronJob;

import java.io.BufferedReader;
import java.io.IOException;
import java.sql.*;
import java.util.UUID;

/**
 * @Title: ecology-9-demo
 * @Company: 泛微软件
 * @author: weaver.Dusongsong
 * @version: 1.0
 * @CreateDate: 2020-06-05
 * @Description: 同步亚微提供的邮件信息
 * @ModifyLog:
 **/

public class MailCronJobSyn extends BaseCronJob {
    @Override
    public void execute() {
        BaseBean baseBean = new BaseBean();
        Connection conn = null;
        //发件人信息
        String time = TransMethod.getTime();
        String timebefore = TransMethod.getTimebefore();
        baseBean.writeLog( "----MailCronJobSyn----timebefore=" + timebefore + ",curtime=" + time );
        RecordSetDataSource ds1 = new RecordSetDataSource( "YaWei" );//外部数据源标识
        DataSource ds = (DataSource) StaticObj.getServiceByFullname( ("datasource.YaWei"), DataSource.class );
        conn = ds.getConnection();
        RecordSet rs = new RecordSet();
        RecordSet rs1 = new RecordSet();
        RecordSet rs2 = new RecordSet();
        RecordSet rs3 = new RecordSet();
        RecordSet rs4 = new RecordSet();
        RecordSet rs5 = new RecordSet();
        RecordSet rs6 = new RecordSet();
        String sql = "select t.GUID,t.title,t.sendPersonGuid,t.createTime,t.IsRollBack ,f.fjsl,f.size_n,p.ReceivePersonGuid,c.content " +
                "from NotePaper t left join (select GUID,count(GUID) as fjsl, sum(fileLength) as size_n from Attachmentinfo group by GUID) f on f.GUID=t.GUID " +
                "left join NotePaperPerson p on t.GUID=p.GUID " +
                "left join NotePaperContent c on t.GUID=c.GUID " +
                "where t.fileSort='邮件' and t.status='2' and createTime between '" + timebefore + "' and '" + time + "'";
        ds1.execute( sql );
        baseBean.writeLog( "查询到亚微" + timebefore + "-" + time + "有邮件" + ds1.getColCounts() + "条" );
        int count = 0;
        while (ds1.next()) {
            String GUID = Util.null2String( ds1.getString( "GUID" ) );//唯一标识
            rs6.executeQuery( "select guid from yw_notepaper where guid='" + GUID + "'" );
            int isRepeat = 0;
            if (!rs6.next()) {
                String sendPersonGuid = Util.null2String( ds1.getString( "sendPersonGuid" ) );//发送人ID
                String sendfrom = TransMethod.HRtransGUIDtoID( sendPersonGuid );
                String priority = "3";
                String senddate = Util.null2String( ds1.getString( "createTime" ) ).substring( 0,19 );//创建日期
                String size_n = Util.null2String( ds1.getString( "size_n" ) );//附件数量
                size_n = "".equals( size_n ) ? "0" : size_n;
                String content = Util.null2String( ds1.getString( "content" ) );//内容
                String subject = Util.null2String( ds1.getString( "title" ) );//标题
                String mailtype = "0";
                String attachmentnumber = Util.null2String( ds1.getString( "fjsl" ) );//附件数量
                attachmentnumber = "".equals( attachmentnumber ) ? "0" : attachmentnumber;
                String status = "1";//邮件状态-0:未读，1:已读
                String folderId = "-1";//文件夹id
                String mailAccountId = "-1";//邮件帐户id
                String haseml = "1";//是否有eml文件
                String isInternal = "1";//内部邮件-1：是内部邮件
                String canview = "1";//可查看
                String readdate = "0";//阅读时间
                String timingdatestate = "-2";//定时发送状态
                String needreceipt = Util.null2String( ds1.getString( "IsRollBack" ) );//是否需要回执0-否 1-是
                String receiveneedreceipt = "1";
                String opSendMessage = "0";
                UUID MR_UUID = UUID.randomUUID();
                String mr_uuid = MR_UUID.toString(); //已发送邮件唯一标志
                String isSendApart = "0";
                UUID MIT_UUID = UUID.randomUUID();
                String mit_uuid = MIT_UUID.toString(); //唯一标志uuid
                String isNewContent = "1";
                UUID CONTENT_UUID = UUID.randomUUID();
                String content_uuid = CONTENT_UUID.toString(); //唯一标志uuid
                //同步发件人
                int count1 = 0;
                if (!"".equals( sendfrom.trim() ) || sendfrom != null) {
                    String sql1 = "insert into mailresource (  RESOURCEID,  PRIORITY,  SENDFROM,sendcc_bak, SENDDATE,  SIZE_N,  `SUBJECT`," +
                            "  content,  MAILTYPE,  ATTACHMENTNUMBER,  `STATUS`,  FOLDERID, MAILACCOUNTID,  HASEML," +
                            "  ISINTERNAL,  CANVIEW, READDATE, TIMINGDATESTATE,  NEEDRECEIPT, MR_UUID, isSendApart," +
                            "  mit_uuid, isNewContent, content_uuid, opSendMessage) " +
                            "values('" + sendfrom + "','" + priority + "','" + sendfrom + "','"+GUID+"','" + senddate + "','" + size_n + "'," +
                            "'" + subject + "','" + content + "','" + mailtype + "','" + attachmentnumber + "','" + status + "','" + folderId + "'," +
                            "'" + mailAccountId + "','" + haseml + "','" + isInternal + "','" + canview + "','" + readdate + "','" + timingdatestate + "'," +
                            "'" + needreceipt + "','" + mr_uuid + "','" + isSendApart + "','" + mit_uuid + "','" + isNewContent + "','" + content_uuid + "'," +
                            "'" + opSendMessage + "')";
                    rs1.writeLog( sql1 );
                    if (rs1.executeUpdate( sql1 )) {
                        baseBean.writeLog( GUID + ":已经同步完成发件人信息记录" );
                        count1++;
                    }
                    baseBean.writeLog( timebefore + "-" + time + "已经同步完成发件人信息记录[" + count1 + "]条" );
                }

                //同步收件人
                String ReceivePersonGuids = Util.null2String( ds1.getString( "ReceivePersonGuid" ) );//接收人ID
                baseBean.writeLog( "ReceivePersonGuids=" + ReceivePersonGuids );
                if (!"".equals( ReceivePersonGuids.trim() ) || ReceivePersonGuids != null) {
                    String[] arr = ReceivePersonGuids.split( ";" );
                    int count4 = 0;
                    for (String ReceivePersonGuid : arr) {
                        String resourceid = TransMethod.HRtransGUIDtoID( ReceivePersonGuid );
                        baseBean.writeLog( "resourceid=" + resourceid );
                        if (!"".equals( resourceid.trim() ) || resourceid != null) {
                            status = "0";
                            folderId = "0";
                            mailAccountId = "0";
                            rs.executeQuery( "select id from mailresource where mr_uuid='" + mr_uuid + "'" );
                            rs.next();
                            String originalmailid = Util.null2String( rs.getString( "id" ) );
                            String sql2 = "insert into mailresource(RESOURCEID,PRIORITY,SENDFROM,sendcc_bak,SENDDATE,SIZE_N,`SUBJECT`,content,MAILTYPE," +
                                    "ATTACHMENTNUMBER,`STATUS`,FOLDERID,MAILACCOUNTID,HASEML,ORIGINALMAILID,ISINTERNAL," +
                                    "CANVIEW,READDATE,RECEIVENEEDRECEIPT,messageid,mit_uuid,isNewContent,content_uuid )" +
                                    "values('" + resourceid + "','" + priority + "','" + sendfrom + "','"+GUID+"','" + senddate + "','" + size_n + "','" + subject + "','" + content + "','" + mailtype + "'," +
                                    "'" + attachmentnumber + "','" + status + "','" + folderId + "','" + mailAccountId + "','" + haseml + "','" + originalmailid + "'," + "'" + isInternal + "','"
                                    + canview + "','" + readdate + "','" + receiveneedreceipt + "','" + mit_uuid + "','" + isNewContent + "','" + content_uuid + "' )";
                            baseBean.writeLog(sql2);
                            if (rs2.executeUpdate( sql2 )) {
                                baseBean.writeLog( GUID + ":已经同步完成收件人信息记录" );
                                count4++;
                                //关联发件人信息

                            }
                        }
                    }
                    rs4.executeUpdate( "insert into mail_internalto(mit_uuid,toids,ccids,bccids) values('" + mit_uuid + "','" + sendfrom + "','','')" );
                    baseBean.writeLog( timebefore + "-" + time + "已经同步完成收件人信息记录[" + count4 + "]条" );
                }

                //存放邮件内容表
                rs.executeQuery( "select id from mailresource where mr_uuid='" + mr_uuid + "'" );
                if (rs.next()) {
                    String mailid = Util.null2String( rs.getString( "id" ) );
                    rs3.executeUpdate( "INSERT INTO mailcontent ( mailid, mailcontent, content_uuid )VALUES('" + mailid + "','" + content + "','" + content_uuid + "')" );
                }

                //用户邮件附件信息表
                String SQL2 = "select a.GUID,a.BLOBGUID,a.attachmentType,a.title,a.fileLength,a.fileExt,a.createTime,i.content " +
                        "from Attachmentinfo a left join BLOBInfo i on a.GUID=i.GUID and a.BlobGuid=i.BLOBGUID where i.GUID ='" + GUID + "'";
                baseBean.writeLog( SQL2 );
                try {
                    PreparedStatement pstm = conn.prepareStatement( SQL2 );
                    ResultSet rs11 = pstm.executeQuery();
                    int count2 = 0;
                    while (rs11.next()) {
                        String filetype = "application/vnd.openxmlformats-officedocument.wordprocessingml.document";//附件类型
                        String title = Util.null2String( rs11.getString( "title" ) );//附件名称
                        String fileLength = Util.null2String( rs11.getString( "fileLength" ) );//附件大小
                        Clob clob = rs11.getClob( "content" );
                        String readline = "";
                        StringBuffer sb = new StringBuffer();
                        BufferedReader br = new BufferedReader( clob.getCharacterStream() );
                        try {
                            while ((readline = br.readLine()) != null) {
                                sb = sb.append( readline );
                            }
                            br.close();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                        String content1 = sb.toString();
                        UUID MRF_UUID = UUID.randomUUID();
                        String mrf_uuid = MRF_UUID.toString(); //唯一标志uuid
                        String ym = TransMethod.getCurYearAndMonth();
                        char randomChar = TransMethod.getRandomChar();
                        String filerealpath = "/u01/weaver/ecology/filesystem/" + ym + "/" + randomChar + "/" + mrf_uuid + "";//文件路径
                        TransMethod.hexToFile( content1, filerealpath );
                        String iszip = "0";
                        String isencrypt = "0";
                        String isfileattrachment = "1";
                        rs.executeQuery( "select id from mailresource where mit_uuid='" + mit_uuid + "'" );
                        while (rs.next()) {
                            String mailid = Util.null2String( rs.getString( "id" ) );
                            String sql5 = "insert into mailresourcefile (mailid,mrf_uuid,filetype,filename,filesize,filerealpath,iszip,isencrypt,isfileattrachment) " +
                                    "values('" + mailid + "','" + mrf_uuid + "','" + filetype + "','" + title + "','" + fileLength + "','" + filerealpath + "','" + iszip + "','" + isencrypt + "','" + isfileattrachment + "')";
                            rs5.executeUpdate( sql5 );
                            count2++;
                        }
                    }
                    baseBean.writeLog( GUID + "-已经同步完成附件信息记录[" + count2 + "]条" );
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                //更新记录表
                String sql6 = "insert into yw_notepaper(guid,sendPersonGuid,ReceivePersonGuid,createTime,opeareType,mit_uuid) values('" + GUID + "','" + sendPersonGuid + "','" + ReceivePersonGuids + "','" + senddate + "', '" + isRepeat + "','" + mit_uuid + "')";
                rs6.executeUpdate( sql6 );
                isRepeat = 0;
            } else {
                String sql6 = "insert into yw_notepaper(guid,sendPersonGuid,ReceivePersonGuid,createTime,opeareType,mit_uuid) values('" + GUID + "','','','', '" + isRepeat + "','" + count + "')";
                rs6.executeUpdate( sql6 );
                isRepeat = 1;
                continue;
            }
            boolean flag = isRepeat == 0 ? true : false;
            baseBean.writeLog( "===========>[" + GUID + "]邮件是否重复：[" + flag + "]" );
            count++;
        }
        baseBean.writeLog( timebefore + "-" + time + "收发件人信息同步完成记录数：[" + count + "]条" );
        rs.executeUpdate( "update syntimemail set beforetime = '" + timebefore + "',curtime='" + time + "' where id ='1'" );
    }
}

